package org.nerdpod.feed;

import com.google.gdata.client.ClientLoginAccountType;
import com.google.gdata.client.GoogleService;
import com.google.gdata.client.Service.GDataRequest;
import com.google.gdata.client.Service.GDataRequest.RequestType;

import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ContentType;
import com.google.gdata.util.ServiceException;

import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLEncoder;
import java.util.List;
import java.util.Scanner;
import java.util.regex.MatchResult;
import java.util.regex.Pattern;

public class FusionTableHelper {

	private static final String username = "janetcbrownstone@gmail.com";
	private static final String password = "janetcbrownstone1";

	private static final String TABLE_URL = "http://tables.googlelabs.com/DataSource?dsrcid=120406";
	private static final String TABLE_ID = "120406";
	private static final String SERVICE_URL = "http://tables.googlelabs.com/api/query";
	private static final Pattern CSV_VALUE_PATTERN = Pattern
			.compile("([^,\\r\\n\"]*|\"(([^\"]*\"\")*[^\"]*)\")(,|\\r?\\n)");

	public FusionTableHelper() {

	}

	public static String addIncidents(List<IncidentBean> incidents) {
		StringBuffer response = new StringBuffer();
		GoogleService service;
		service = new GoogleService("fusiontables", "fusiontables.DisasterFeed");
		// service.setCookieManager(null);
		// System.out.println("Processing " + incidents.size() +
		// " incidents...");
		try {
			service.setUserCredentials(username, password,
					ClientLoginAccountType.GOOGLE);
		} catch (AuthenticationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		for (IncidentBean incident : incidents) {
			try {
				Thread.sleep(250);
			} catch (InterruptedException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			final String selectQuery = "select rowid from " + TABLE_ID +
			// " where PublishDate = '" + incident.getIncidentDate() + "'" +
					" where GEO = '" + incident.getLocation() + "'";
			System.out.println(selectQuery);
			try {
				URL url = new URL(SERVICE_URL + "?sql="
						+ URLEncoder.encode(selectQuery, "UTF-8"));
				GDataRequest request = service.getRequestFactory().getRequest(
						RequestType.QUERY, url, ContentType.TEXT_PLAIN);

				request.execute();

				Scanner scanner = new Scanner(request.getResponseStream());

				int count = 0;
				boolean exists = false;
				while (scanner.hasNextLine()) {
					if (count++ > 0) {
						// a row is found
						exists = true;
						continue;
					}
					scanner.findWithinHorizon(CSV_VALUE_PATTERN, 0);
					MatchResult match = scanner.match();
					String quotedString = match.group(2);
					String decoded = quotedString == null ? match.group(1)
							: quotedString.replaceAll("\"\"", "\"");
					System.out.print("|" + decoded);
					if (!match.group(4).equals(",")) {
						System.out.println("|");
					}
				}
				request.end();
				scanner.close();
				if (!exists) {
					// insert new row
					final String updateQuery = "insert into " + TABLE_ID
							+ " (PublishDate, Title, URL, GEO) values " + "('"
							+ incident.getIncidentDate() + "', '"
							+ incident.getTitle() + "', '" + incident.getUri()
							+ "', '" + incident.getLocation() + "')";
					System.out.println("Inserting row: " + updateQuery);
					url = new URL(SERVICE_URL);
					request = service
							.getRequestFactory()
							.getRequest(
									RequestType.INSERT,
									url,
									new ContentType(
											"application/x-www-form-urlencoded"));
					OutputStreamWriter writer = new OutputStreamWriter(request
							.getRequestStream());
					writer.append("sql="
							+ URLEncoder.encode(updateQuery, "UTF-8"));
					writer.flush();

					request.execute();
					request.end();

				}

			} catch (MalformedURLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (ServiceException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return response.toString();
	}

	public static void main(String[] args) {
		GoogleService service;
		service = new GoogleService("fusiontables", "fusiontables.ApiExample");
		String selectQuery = "SELECT PublishDate, Title, GEO from " + TABLE_ID;
		try {
			service.setUserCredentials(username, password,
					ClientLoginAccountType.GOOGLE);
			URL url = new URL(SERVICE_URL + "?sql="
					+ URLEncoder.encode(selectQuery, "UTF-8"));
			GDataRequest request = service.getRequestFactory().getRequest(
					RequestType.QUERY, url, ContentType.TEXT_PLAIN);

			request.execute();

			/* Prints the results of the query. */
			/* No Google Fusion Tables API-specific code here. */

			Scanner scanner = new Scanner(request.getResponseStream());
			while (scanner.hasNextLine()) {

				scanner.findWithinHorizon(CSV_VALUE_PATTERN, 0);
				MatchResult match = scanner.match();
				String quotedString = match.group(2);
				String decoded = quotedString == null ? match.group(1)
						: quotedString.replaceAll("\"\"", "\"");
				System.out.print("|" + decoded);
				if (!match.group(4).equals(",")) {
					System.out.println("|");
				}
			}

		} catch (AuthenticationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (MalformedURLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ServiceException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}
